package com.example.projectdemo.config;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.projectdemo.domain.Place;
import com.example.projectdemo.domain.Tea;
import com.example.projectdemo.domain.User;

import java.util.ArrayList;

public class DBOpenHelper extends SQLiteOpenHelper {

    private SQLiteDatabase db;

    public DBOpenHelper(Context context) {//打开数据库
        //1：上下文，2：数据库名，3：允许我们查询数据时返回一个Cursor，4：当前数据库的版本号
        super(context, "db1", null, 1);
        db = getReadableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {//建表（user）语句
        db.execSQL("CREATE TABLE IF NOT EXISTS User(" +//PRIMARY key 将id设为主键 ，AUTOINCREMENT 设置id列自为增长
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +                       //text 文本类型
                "password TEXT)");

        db.execSQL("CREATE TABLE IF NOT EXISTS Tea(" +//PRIMARY key 将id设为主键 ，AUTOINCREMENT 设置id列自为增长
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +                       //text 文本类型
                "price TEXT)");
        db.execSQL("CREATE TABLE IF NOT EXISTS Tea2(" +//PRIMARY key 将id设为主键 ，AUTOINCREMENT 设置id列自为增长
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +                       //text 文本类型
                "price TEXT)");
        db.execSQL("CREATE TABLE IF NOT EXISTS Chafing(" +//PRIMARY key 将id设为主键 ，AUTOINCREMENT 设置id列自为增长
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +                       //text 文本类型
                "price TEXT)");
        db.execSQL("CREATE TABLE IF NOT EXISTS Place(" +//PRIMARY key 将id设为主键 ，AUTOINCREMENT 设置id列自为增长
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +                       //text 文本类型
                "place TEXT," +                       //text 文本类型
                "remark TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//重写升级
        db.execSQL("DROP TABLE IF EXISTS User");
        db.execSQL("DROP TABLE IF EXISTS Tea");
        db.execSQL("DROP TABLE IF EXISTS Tea2");
        db.execSQL("DROP TABLE IF EXISTS Chafing");
        db.execSQL("DROP TABLE IF EXISTS Place");
        onCreate(db);
    }

    public void add(String name, String password) {//重写添加
        db.execSQL("INSERT INTO user (name,password) VALUES(?,?)", new Object[]{name, password});

    }

    public void add2(String name, String price) {
        db.execSQL("INSERT INTO Tea (name,price) VALUES(?,?)", new Object[]{name, price});
    }

    public void add3(String name, String price) {
        db.execSQL("INSERT INTO Tea2 (name,price) VALUES(?,?)", new Object[]{name, price});
    }

    public void add4(String name, String price) {
        db.execSQL("INSERT INTO Chafing (name,price) VALUES(?,?)", new Object[]{name, price});
    }

    public void add5(String name, String place, String remark, String phone) {
        db.execSQL("INSERT INTO Chafing (name,place,remark,phone) VALUES(?,?)", new Object[]{name, place, remark, phone});
    }

    public void delete(String name, String password) {//重写删除
        db.execSQL("DELETE FROM user WHERE name = AND password =" + name + password);
    }

    public void updata(String password) {//重写更新
        db.execSQL("UPDATE user SET password = ?", new Object[]{password});
    }

    @SuppressLint("Range")
    public ArrayList<User> getAllData() {//将表内信息返回成一个list

        ArrayList<User> list = new ArrayList<User>();
        //1表名，2列，3行，4行，5指定列进行过滤，6进一步过滤。7得到的信息进行排序（desc逆序）
        Cursor cursor = db.query("user", null, null, null, null, null, "name DESC");
        while (cursor.moveToNext()) {//一行一行遍历
            String name = cursor.getString(cursor.getColumnIndex("name"));//移动到name列，读取出来
            String password = cursor.getString(cursor.getColumnIndex("password"));
            list.add(new User(name, password));//添加到user 的list中
        }
        return list;//把list返回
    }

    @SuppressLint("Range")
    public ArrayList<Tea> getAllData2() {//将表内信息返回成一个list

        ArrayList<Tea> list = new ArrayList<Tea>();
        //1表名，2列，3行，4行，5指定列进行过滤，6进一步过滤。7得到的信息进行排序（desc逆序）
        Cursor cursor = db.query("tea", null, null, null, null, null, "name DESC");
        while (cursor.moveToNext()) {//一行一行遍历
            String name = cursor.getString(cursor.getColumnIndex("name"));//移动到name列，读取出来
            String price = cursor.getString(cursor.getColumnIndex("price"));
            list.add(new Tea(name, price));//添加到user 的list中
        }
        return list;//把list返回
    }

    @SuppressLint("Range")
    public ArrayList<Place> getAllData3() {//将表内信息返回成一个list

        ArrayList<Place> list = new ArrayList<Place>();
        //1表名，2列，3行，4行，5指定列进行过滤，6进一步过滤。7得到的信息进行排序（desc逆序）
        Cursor cursor = db.query("place", null, null, null, null, null, "name DESC");
        while (cursor.moveToNext()) {//一行一行遍历
            String name = cursor.getString(cursor.getColumnIndex("name"));//移动到name列，读取出来
            String place = cursor.getString(cursor.getColumnIndex("place"));
            String remark = cursor.getString(cursor.getColumnIndex("remark"));
            list.add(new Place(name, place, remark));//添加到user 的list中
        }
        return list;//把list返回
    }
}


